<?php
class Classified_model extends CI_Model {

	/**
	 * @author Pwint Phyu Shwe
	 * Advertisement Model for tbl_classified and tbl_adv_category tables
	 * CRUD of Classifieds related.
	 * This model include pagination and related functions.
	 * Refer detail in each function's comment section below.
	 * This model is related to classified advertisements.
	 */
	// table name
	private $tbl_classified = 'classified';
	private $tbl_user = 'masteruser';
	private $tbl_advcategory = 'adv_category';

	function __construct()
	{
		// Call the Model constructor
		parent::__construct();
	}
	// get number of users in database
	function count_all(){
		return $this->db->count_all($this->tbl_classified);
	}
	/**
	get totoal number of result in database
	return $count
	**/
	function count_result($cond = NULL){
		//filter
		if ($cond != NULL){
			foreach ($cond as  $param => $item)
				$this->db->where($param , $item);
		}
		$this->db->where('expired_date > NOW()');
		
		$this->db->select('COUNT(advid) as count');
		$this->db->from($this->tbl_classified);
		$this->db->join($this->tbl_user, 'masteruser.userID = classified.advertiserID', 'left');
		$this->db->join($this->tbl_advcategory, 'adv_category.id = classified.categoryID', 'left');
		
		$query = $this->db->get();
		
		$results = $query->result();
		$num_rows = $results[0]->count;
		$query->free_result();
		return $num_rows;
	}
	// get advertisement by offset, limit
	function get_result($cond = NULL, $limit = 10, $offset = 0, $sort = NULL){
		//filter
		if ($cond != NULL){
			foreach ($cond as  $param => $item)
				$this->db->where($param , $item);
		}
		
		$this->db->where('expired_date > NOW()');
		//sorting
		if ($sort != NULL){
			for ($i = 0; $i < count($sort); $i++)
				$this->db->order_by($sort[$i]['column'], $sort[$i]['order']);
		}
		//limit & offset
		$this->db->limit($limit, $offset);
		
		$this->db->select('*', 'adv_category.cat_descriptions', 'masteruser.firstName', 'masteruser.lastName');
		$this->db->from($this->tbl_classified);
		//$this->db->where('advid', $id);
		$this->db->join($this->tbl_user, 'masteruser.userID = classified.advertiserID', 'left');
		$this->db->join($this->tbl_advcategory, 'adv_category.id = classified.categoryID', 'left');
		
		$query = $this->db->get();
		$results['rows'] = $query->result();
		$results['num_rows'] = $this->count_result($cond);
		$query->free_result(); //free result
		return $results;
	}
	// get by price range, cond will have 'low' and 'high' price range as array
	function get_by_pricerange($price_range = NULL, $cond=NULL, $limit = 10, $offset = 0, $sort = NULL){
		
		if ($price_range == NULL)
			return false;
		//condition
		if ($cond != NULL){
			foreach ($cond as  $param => $item)
				$this->db->where($param , $item);
		}
		//sorting
		if ($sort != NULL){
			for ($i = 0; $i < count($sort); $i++)
				$this->db->order_by($sort[$i]['column'], $sort[$i]['order']);
		}
		 
		//limit & offset
		$this->db->limit($limit, $offset);
		$this->db->select('*', 'adv_category.cat_descriptions', 'masteruser.firstName', 'masteruser.lastName');
		$this->db->from($this->tbl_classified);
		$this->db->where('price between ' . $price_range['low']. ' and ' . $price_range['high']);
		$this->db->join($this->tbl_user, 'masteruser.userID = classified.advertiserID', 'left');
		$this->db->join($this->tbl_advcategory, 'adv_category.id = classified.categoryID', 'left');
		$query = $this->db->get();
		
		return $query->result_array();
	}
	// get search result
	function get_search_result($search = NULL, $cond = NULL, $limit = 10, $offset = 0, $sort = NULL)
	{
		//filter
		if ($cond != NULL){
			foreach ($cond as  $param => $item)
				$this->db->where($param , $item);
		}
		$this->db->where('expired_date > NOW()');
		//sorting
		if ($sort != NULL){
			for ($i = 0; $i < count($sort); $i++)
				$this->db->order_by($sort[$i]['column'], $sort[$i]['order']);
		}
		//limit & offset
		$this->db->limit($limit, $offset);
		
		$this->db->select('*', 'adv_category.cat_descriptions', 'masteruser.firstName', 'masteruser.lastName');
		$this->db->from($this->tbl_classified);
		if ($search != NULL){
			$this->db->where("(`title` LIKE  '%" . $search ."%' OR  `description` LIKE  '%". $search ."%')");
		}
		$this->db->join($this->tbl_user, 'masteruser.userID = classified.advertiserID', 'left');
		$this->db->join($this->tbl_advcategory, 'adv_category.id = classified.categoryID', 'left');
		$query = $this->db->get();
		
		$results['rows'] = $query->result();
		$results['num_rows'] = $this->count_search_result($search, $cond);
		$query->free_result(); //free result
		return $results;
	}
	/**
	get totoal number of search result in database
	return $count
	**/
	function count_search_result($search=NULL, $cond = NULL){
		//filter
		if ($cond != NULL){
			foreach ($cond as  $param => $item)
				$this->db->where($param , $item);
		}
		
		$this->db->select('COUNT(*) as count');
		$this->db->from($this->tbl_classified);
		if ($search != NULL){
			$this->db->where("(`title` LIKE  '%" . $search ."%' OR  `description` LIKE  '%". $search ."%')");
		}
		$this->db->where('expired_date > NOW()');
		$this->db->join($this->tbl_user, 'masteruser.userID = classified.advertiserID', 'left');
		$this->db->join($this->tbl_advcategory, 'adv_category.id = classified.categoryID', 'left');
		
		$query = $this->db->get();
		
		$results = $query->result();
		$num_rows = $results[0]->count;
		$query->free_result();
		return $num_rows;
	}
	// add new advertisement
	function save($advertisement){
		//current & expiry date
		//$current_date = date('d-m-Y H:m:s');
		//$advertisement['posted_date'] = $current_date;
		//$this->load->helper('common_functions');
		//$advertisement['expiry_date'] = $this->common_functions->calculate_expiry_date($current_date, '+14 day');
		
		$this->db->insert($this->tbl_classified, $advertisement);
		return $this->db->insert_id();
	}
	// update person by userID
	function update($updated_adv){
	
		$advertisement['advid']=$updated_adv['advid'];
		$advertisement['categoryID'] = $updated_adv['categoryID'];
		$advertisement['advertiserID'] = $updated_adv['advertiserID'];
		
		$advertisement['title'] = $updated_adv['title'];
		$advertisement['description'] = $updated_adv['description'];
		$advertisement['price'] = $updated_adv['price'];
		$advertisement['quantity'] = $updated_adv['quantity'];
		$advertisement['isSpecialDiscount'] = $updated_adv['isSpecialDiscount'];
		$advertisement['percentage'] = $updated_adv['percentage'];
		$advertisement['discounted_price'] = $updated_adv['discounted_price'];
		
		$advertisement['contact_person'] = $updated_adv['contact_person'];
		$advertisement['contact_email'] = $updated_adv['contact_email'];
		$advertisement['contact_number'] = $updated_adv['contact_number'];
		
		$advertisement['posted_date'] = $updated_adv['posted_date'];
		$advertisement['expiry_date'] = $updated_adv['expiry_date'];
	
	
		$this->db->where('advid',$advertisement['advid']);
		$this->db->update($this->tbl_classified, $advertisement);
	}

	// delete person by advertiserID
	function delete($advid){
		$this->db->where('advid', $advid);
		$this->db->delete($this->tbl_classified);
	}
	/**
	 * Get No of post by user
	 * @param  [type] $advertiserID [description]
	 * @return [type]               [description]
	 */
	function get_statistics($cond = NULL){
		//filter
		if ($cond != NULL){
			foreach ($cond as  $param => $item)
				$this->db->where($param , $item);
		}
		$this->db->select('YEAR(`posted_date`) AS "year", MONTH(`posted_date`) AS "month", COUNT(`advid`) AS "Posts"');
		$this->db->from($this->tbl_classified);
		$this->db->group_by(array("year", "month"));
		$query = $this->db->get();
		$points = array();
		foreach ($query->result() as $row)
		{
		   $points[] =  (int)$row->Posts;
		}
		//log_message('INFO',"Query---------->" . $this->db->last_query());
		return $points;
	}

	function _calcuate_discount($price, $percentage){
		return $price - (($price * (int)$percentage) / 100);

	}

}
/* End of file classified_model.php */
/* Location: ./application/models/classified_model.php */
?>